package com.xms.sdk.query;

import com.xms.core.enums.AccessRightValue;
import com.xms.core.enums.EntityPermissionDepth;
import com.xms.core.exception.XmsException;
import com.xms.core.exception.XmsUnauthorizedException;
import com.xms.datacore.DataBaseSetting;
import com.xms.datacore.DataBaseType;
import com.xms.datacore.SqlDescriptor;
import com.xms.schema.core.EntityMask;
import com.xms.schema.entity.Attribute;
import com.xms.schema.entity.Entity;
import com.xms.schema.entity.OptionSetDetail;
import com.xms.schema.entity.RelationShip;
import com.xms.schema.extension.AttributeExt;
import com.xms.schema.service.attribute.IAttributeFinderService;
import com.xms.sdk.core.RetrieverParameter;
import com.xms.sdk.core.ValueConverter;
import com.xms.sdk.core.query.*;
import com.xms.sdk.extensions.ConditionExpressionValidator;
import com.xms.security.entity.RoleEntityPermission;
import com.xms.utils.CollectionUtil;
import com.xms.utils.StringUtil;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.ArrayList;
import java.util.Comparator;
import java.util.Iterator;
import java.util.List;
import java.util.stream.Collectors;

/**
 * <p>
 * QueryExpressionResolver
 * </p>
 *
 * @author migo
 * @since 2021/9/4
 */
@Service
public class QueryExpressionResolver implements IQueryResolver {
    private Logger _logger = LoggerFactory.getLogger(getClass());
    private IAttributeFinderService _attributeFinderService;

    @Autowired
    public void QueryExpressionResolver(IAttributeFinderService attributeFinderService) {
        _attributeFinderService = attributeFinderService;
    }

    @Override
    public SqlDescriptor toSqlString(RetrieverParameter retrieverParameter, QueryMetadataDescriptor metadataDescriptor) throws XmsException {
        QueryExpression queryExpression = (QueryExpression) retrieverParameter.getQuery();
        QueryParameters sqlParameters = new QueryParameters();
        metadataDescriptor.attributeAlias = new ArrayList<>();
        List<AttributeAlias> attributeAlias = metadataDescriptor.attributeAlias;
        Entity mainEntity = metadataDescriptor.entityList.stream().filter(x -> x.getName().equalsIgnoreCase(queryExpression.EntityName)).findFirst().get();
        if (queryExpression.ColumnSet.AllColumns && CollectionUtil.isEmpty(queryExpression.ColumnSet.Columns)) {
            queryExpression.ColumnSet.addColumns(metadataDescriptor.attributeList.stream().filter(n -> n.getEntityname().equalsIgnoreCase(queryExpression.EntityName)).map(m -> m.getName()).collect(Collectors.toList()));
        }
        Attribute primaryAttr = metadataDescriptor.attributeList.stream().filter(n -> n.getEntityname().equalsIgnoreCase(queryExpression.EntityName) && AttributeExt.typeIsPrimaryKey(n)).findFirst().orElse(null);
        if (primaryAttr == null) {
            primaryAttr = _attributeFinderService.getPrimaryKey(queryExpression.EntityName);
        }
        if (!queryExpression.ColumnSet.AllColumns && !queryExpression.ColumnSet.Columns.contains(primaryAttr.getName())) {
            queryExpression.ColumnSet.addColumn(primaryAttr.getName().toLowerCase());
        }
        StringBuilder sqlString = new StringBuilder();
        List<String> tableList = new ArrayList<>();
        List<String> attrList = new ArrayList<>();
        List<String> filterList = new ArrayList<>();
        List<String> orderList = new ArrayList<>();
        String mainEntityAlias = queryExpression.EntityName;
        //tables
        tableList.add(queryExpression.EntityName + "view AS " + wrapField(mainEntityAlias));
        //columns
        for (String column : queryExpression.ColumnSet.Columns) {
            String field = column.toLowerCase();
            Attribute attr = metadataDescriptor.attributeList.stream().filter(x -> x.getName().equalsIgnoreCase(column) && x.getEntityname().equalsIgnoreCase(queryExpression.EntityName)).findFirst().orElse(null);
            if (queryExpression.ColumnSet.ColumnFormatting.containsKey(field))//if has formatting
            {
                String formatting = queryExpression.ColumnSet.ColumnFormatting.get(field);
                attrList.add(wrapField(formatting) + " AS " + wrapField(column.toLowerCase() + "_formatting"));
            }
            if (attr != null) {
                boolean nullRead = retrieverParameter.getNoneReadFields() != null ? retrieverParameter.getNoneReadFields().stream().anyMatch(x -> x.getAttributeid().equals(attr.getAttributeid())) : false;
                AttributeAlias attrAlias = new AttributeAlias();
                attrAlias.EntityName = queryExpression.EntityName;
                attrAlias.EntityAlias = mainEntityAlias;
                attrAlias.Name = attr.getName();
                attrAlias.Alias = attr.getName();
                attributeAlias.add(attrAlias);

                //guid类型的字段值，将包含对应的名称字段
                if (retrieverParameter.isIncludeNameField() && AttributeExt.typeIsRef(attr)) {
                    field += "name";
                    field = wrapField(field);
                    if (nullRead) {
                        attrList.add("null AS " + field);
                    } else {
                        attrList.add(wrapField(mainEntityAlias) + "." + field);
                    }
                }
                if (AttributeExt.typeIsState(attr) || AttributeExt.typeIsBit(attr)) {
                    if (nullRead) {
                        attrList.add("null AS " + wrapField(field));
                    } else {
                        attrList.add(wrapField(mainEntityAlias) + "." + wrapField(field));
                        field = String.format("(CASE %s WHEN '0' THEN '%s' WHEN '1' THEN '%s' ELSE '' END) AS %s"
                                , wrapField(mainEntityAlias) + "." + wrapField(field)
                                , attr.getPicklists().stream().filter(n -> n.getValue() == 0).findFirst().get().getName()
                                , attr.getPicklists().stream().filter(n -> n.getValue() == 1).findFirst().get().getName()
                                , wrapField(field + "name"));
                        attrList.add(field);
                    }
                } else if (AttributeExt.typeIsPickList(attr) || AttributeExt.typeIsStatus(attr)) {
                    if (nullRead) {
                        attrList.add("null AS " + field);
                        attrList.add(String.format("null AS {0}", wrapField(field + "name")));
                    } else {
                        attrList.add(wrapField(mainEntityAlias) + "." + wrapField(field));
                        if (CollectionUtil.notEmpty(attr.getOptionset().getItems())) {
                            String pField = String.format("(CASE %s", wrapField(mainEntityAlias) + "." + wrapField(field));
                            for (OptionSetDetail ops : attr.getOptionset().getItems()) {
                                pField += String.format(" WHEN %s THEN '%s' ", ops.getValue(), ops.getName());
                            }
                            pField += String.format("ELSE '' END) AS %s", wrapField(field + "name"));
                            attrList.add(pField);
                        } else {
                            attrList.add(String.format("'' AS {0}name", wrapField(field + "name")));
                        }
                    }
                } else if (AttributeExt.typeIsDecimal(attr) || AttributeExt.typeIsFloat(attr) || AttributeExt.typeIsMoney(attr)) {
                    if (nullRead) {
                        attrList.add("null AS " + wrapField(field));
                    } else {
                        attrList.add("CAST(ROUND(" + wrapField(mainEntityAlias) + "." + wrapField(field) + "," + attr.getPrecision() + ") AS " + AttributeExt.getDbType(attr) + ") AS " + wrapField(field));
                    }
                } else if (AttributeExt.typeIsDateTime(attr)) {
                    if (nullRead) {
                        attrList.add("null AS " + wrapField(field));
                    } else {
                        if (StringUtil.equalSafe(attr.getDataformat(), "yyyy/MM/dd")) {
                            attrList.add("TO_CHAR(" + wrapField(mainEntityAlias) + "." + wrapField(field) + ", 'yyyy/MM/dd')  AS " + wrapField(field));
                        } else {
                            attrList.add(wrapField(mainEntityAlias) + "." + wrapField(field));
                        }
                    }
                } else {
                    if (nullRead) {
                        attrList.add("null AS " + wrapField(column));
                    } else {
                        attrList.add(wrapField(mainEntityAlias) + "." + wrapField(column));
                    }
                }
            }
        }
        //primary field
        Attribute primaryField = metadataDescriptor.attributeList.stream().filter(n -> n.getEntityname().equalsIgnoreCase(queryExpression.EntityName) && n.getIsprimaryfield()).findFirst().orElse(null);
        if (primaryField != null && retrieverParameter.isIncludeNameField()
                && !attributeAlias.stream().anyMatch(x -> x.EntityName.equalsIgnoreCase(queryExpression.EntityName) && x.EntityAlias.equalsIgnoreCase(mainEntityAlias)
                && x.Name.equalsIgnoreCase(primaryField.getName()) && x.Alias.equalsIgnoreCase(primaryField.getName()))) {
            attrList.add(wrapField(mainEntityAlias) + "." + wrapField(primaryField.getName()));
            AttributeAlias attrAlias = new AttributeAlias();
            attrAlias.EntityName = queryExpression.EntityName;
            attrAlias.EntityAlias = mainEntityAlias;
            attrAlias.Name = primaryField.getName();
            attrAlias.Alias = primaryField.getName();
            attributeAlias.add(attrAlias);
        }
        //filters
        if (!retrieverParameter.isIgnorePermissions() && mainEntity.getAuthorizationenabled()
                && retrieverParameter.getCurrentUser() != null && !retrieverParameter.getCurrentUser().isAdministrator()) {
            if (CollectionUtil.isEmpty(retrieverParameter.getEntityPermissions())) {
                throw new XmsUnauthorizedException("无 '" + mainEntity.getLocalizedname() + "' 读取权限");
            }
            List<RoleEntityPermission> permissionList = retrieverParameter.getEntityPermissions().stream()
                    .filter(n -> n.getEntityId().equals(mainEntity.getEntityid())).collect(Collectors.toList());
            //获取最大权限
            RoleEntityPermission prv = CollectionUtil.notEmpty(permissionList) ?
                    permissionList.stream().max(Comparator.comparing(RoleEntityPermission::getAccessRightsMask)).get() : null;
            if (prv == null || prv.getAccessRightsMask() == EntityPermissionDepth.None) {
                throw new XmsUnauthorizedException("无 '" + mainEntity.getLocalizedname() + "' 读取权限");
            }
            filterList.add("((");
            //basic
            if (prv != null && mainEntity.getEntitymask() == EntityMask.User.value() && prv.getAccessRightsMask() == EntityPermissionDepth.Self) {
                filterList.add(String.format("%s.owneridtype = 1 AND %s.ownerid=?", wrapField(mainEntityAlias), wrapField(mainEntityAlias)));
                sqlParameters.args.add(retrieverParameter.getCurrentUser().getUser().SystemUserId);
            }
            //local
            else if (prv != null && mainEntity.getEntitymask() == EntityMask.User.value() && prv.getAccessRightsMask() == EntityPermissionDepth.BusinessUnit) {
                filterList.add(String.format("%s.owneridtype = 1 AND %s.owningbusinessunit=?", wrapField(mainEntityAlias), wrapField(mainEntityAlias)));
                sqlParameters.args.add(retrieverParameter.getCurrentUser().getUser().BusinessUnitId);
            }
            //deep
            else if (prv != null && mainEntity.getEntitymask() == EntityMask.User.value() && prv.getAccessRightsMask() == EntityPermissionDepth.BusinessUnitAndChild) {
                filterList.add(String.format("%s.owneridtype = 1 AND %s.owningbusinessunit IN(SELECT businessunitid FROM ufn_org_getdepttree(?))", wrapField(mainEntityAlias), wrapField(mainEntityAlias)));
                sqlParameters.args.add(retrieverParameter.getCurrentUser().getUser().BusinessUnitId);
            }
            //full
            else if (prv != null && prv.getAccessRightsMask() == EntityPermissionDepth.Organization) {
                filterList.add(String.format("%s.organizationid=?", wrapField(mainEntityAlias)));
                sqlParameters.args.add(retrieverParameter.getCurrentUser().getUser().OrganizationId);
            }
            filterList.add(")");
            //team owner
            if (mainEntity.getEntitymask() == EntityMask.User.value()) {
                filterList.add(String.format("OR (%s.owneridtype = 3 AND %s.ownerid IN(SELECT teamid FROM teammembership WHERE systemuserid = ?))", wrapField(mainEntityAlias), wrapField(mainEntityAlias)));
                sqlParameters.args.add(retrieverParameter.getCurrentUser().getUser().SystemUserId);
            }
            //shared
            filterList.add(String.format(" OR %s.%s IN(SELECT objectid FROM principalobjectaccess WHERE principalid=? AND entityid=? AND %s.%s=objectid AND accessrightsmask = ?)"
                    , wrapField(mainEntityAlias), wrapField(primaryAttr.getName())
                    , wrapField(mainEntityAlias), wrapField(primaryAttr.getName())));
            filterList.add(")");
            sqlParameters.args.add(retrieverParameter.getCurrentUser().getUser().SystemUserId);
            sqlParameters.args.add(mainEntity.getEntityid());
            sqlParameters.args.add(AccessRightValue.Read.value());
        }
        if (queryExpression.Criteria != null) {
            parseFilter(retrieverParameter, metadataDescriptor, metadataDescriptor.entityList.stream().filter(n -> n.getName().equalsIgnoreCase(queryExpression.EntityName)).findFirst().get()
                    , queryExpression.Criteria.FilterOperator
                    , queryExpression.Criteria, mainEntityAlias, filterList, sqlParameters);
        }
        //link entities
        if (CollectionUtil.notEmpty(queryExpression.LinkEntities)) {
            for (LinkEntity le : queryExpression.LinkEntities) {
                le.FromEntityAlias = mainEntityAlias;
                parseLinkEntity(retrieverParameter, metadataDescriptor, le, tableList, attrList, filterList, sqlParameters);
            }
        }
        //orders
        if (CollectionUtil.notEmpty(queryExpression.Orders)) {
            for (OrderExpression ord : queryExpression.Orders) {
                String ordName = ord.AttributeName;
                if (ord.AttributeName.indexOf(".") > 0) //has alias
                {
                    String[] b = StringUtil.splitSafe(ord.AttributeName, "[.]");
                    RelationShip rs = metadataDescriptor.relationShipList.stream().filter(n -> n.getName().equalsIgnoreCase(b[0])).findFirst().orElse(null);
                    if (rs == null) continue;
                    Attribute a = metadataDescriptor.attributeList.stream().filter(n -> n.getAttributeid().equals(rs.getReferencedattributeid())).findFirst().orElse(null);
                    if (null == a) continue;
                } else {
                    Attribute a = metadataDescriptor.attributeList.stream().filter(n -> n.getName().equalsIgnoreCase(ord.AttributeName) && n.getEntityname().equalsIgnoreCase(mainEntity.getName())).findFirst().orElse(null);
                    if (null == a) {
                        if (ord.AttributeName.length() > 4 && StringUtil.endsWithIgnoreCase(ord.AttributeName, "name")) {
                            a = metadataDescriptor.attributeList.stream().filter(n -> n.getName().equalsIgnoreCase(ord.AttributeName.substring(0, ord.AttributeName.length() - 4))).findFirst().get();
                        }
                    }
                    if (null == a) continue;
                    ordName = String.format("%s.%s", a.getEntityname(), ordName);
                }
                orderList.add(ordName + (ord.OrderType == OrderType.Descending ? " DESC" : ""));
            }
        }
        String tableStr = String.join(" ", tableList).toLowerCase();
        String attrStr = String.join(",", attrList).toLowerCase();
        String filterStr = filterList != null ? String.join(" ", filterList).toLowerCase() : "";
        String orderStr = orderList != null ? String.join(",", orderList).toLowerCase() : "";
        String pagingStr = "";
        if(queryExpression.PageInfo != null && queryExpression.PageInfo.PageSize > 0){
            if(DataBaseSetting.dataBaseType.equals(DataBaseType.SQLSERVER)) {
                pagingStr = ",ROW_NUMBER() over(order by "+orderStr+") as rownum";
            }
        }
        sqlString.append(String.format("SELECT %s %s%s FROM %s %s %s "
                , queryExpression.Distinct ? "DISTINCT" : ""
                , attrStr, pagingStr
                , tableStr, !filterStr.isEmpty() ? " WHERE " + filterStr : ""
                , !orderStr.isEmpty() && pagingStr.isEmpty() ? " ORDER BY " + orderStr : ""));
        SqlDescriptor sql;
        if (queryExpression.Distinct) {
            sql = new SqlDescriptor(String.format("SELECT * FROM (%s) a", sqlString.toString()), sqlParameters.args);
        } else {
            if (sqlParameters.args.size() > 0)
                sql = new SqlDescriptor(sqlString.toString(), sqlParameters.args.toArray());
            else
                sql = new SqlDescriptor(sqlString.toString());
        }
        _logger.info("finalsql is: {}", sql.getFinalSql());
        if (CollectionUtil.notEmpty(sqlParameters.args))
            _logger.info("finalargs is: {}", StringUtil.join(sql.getFinalArgs(), ", "));
        return sql;
    }

    private void parseFilter(RetrieverParameter retrieverParameter, QueryMetadataDescriptor metadataDescriptor, Entity entityMetaData, LogicalOperator logicalOperator, FilterExpression filter, String entityAlias, List<String> filterList, QueryParameters sqlParameters) throws XmsException {
        boolean flag = false;
        if (CollectionUtil.notEmpty(filter.Conditions)) {
            filterList.add((CollectionUtil.notEmpty(filterList) ? (logicalOperator == LogicalOperator.And ? "AND" : "OR") : "") + " (");
            for (ConditionExpression cd : filter.Conditions) {
                if (flag) {
                    filterList.add(filter.FilterOperator == LogicalOperator.And ? "AND" : "OR");
                }
                filterList.add(makeCondition(retrieverParameter, metadataDescriptor, entityMetaData, entityAlias, cd, sqlParameters));
                flag = true;
            }
            if (CollectionUtil.notEmpty(filter.Filters)) {
                for (FilterExpression item : filter.Filters) {
                    parseFilter(retrieverParameter, metadataDescriptor, entityMetaData, filter.FilterOperator, item, entityAlias, filterList, sqlParameters);
                }
            }
            filterList.add(")");
        } else if (CollectionUtil.notEmpty(filter.Filters)) {
            for (FilterExpression item : filter.Filters) {
                parseFilter(retrieverParameter, metadataDescriptor, entityMetaData, filter.FilterOperator, item, entityAlias, filterList, sqlParameters);
            }
        }
    }

    private String makeCondition(RetrieverParameter retrieverParameter, QueryMetadataDescriptor metadataDescriptor, Entity entityMetaData, String entityAliaName, ConditionExpression conditionNode, QueryParameters sqlParameters) throws XmsException {
        boolean isLinkCondition = conditionNode.AttributeName.indexOf('.') > 0; //是否按关联实体的字段过滤
        Attribute attribute = null;
        if (isLinkCondition) {
            String[] arr = StringUtil.splitSafe(conditionNode.AttributeName, "[.]");
            RelationShip relationShip = metadataDescriptor.relationShipList.stream().filter(x -> x.getName().equalsIgnoreCase(arr[0])).findFirst().orElse(null);
            if (relationShip != null) {
                attribute = metadataDescriptor.attributeList.stream().filter(x -> x.getEntityid().equals(relationShip.getReferencedentityid()) && x.getName().equalsIgnoreCase(arr[1])).findFirst().orElse(null);
            }
        } else {
            attribute = metadataDescriptor.attributeList.stream().filter(x -> x.getEntityid().equals(entityMetaData.getEntityid())
                    && x.getName().equalsIgnoreCase(conditionNode.AttributeName)).findFirst().orElse(null);
        }
        if (attribute == null) throw new XmsException("字段不存在：" + conditionNode.AttributeName);
        String condition = "";
        String attrName = (!isLinkCondition ? wrapField(entityAliaName) + "." : "") + wrapField(conditionNode.AttributeName);

        String parameter = "?";

        addParamValue(retrieverParameter, attribute, conditionNode, sqlParameters);
        switch (conditionNode.Operator) {
            case ConditionOperator.Equal:
                condition = String.format("%s=%s", attrName, parameter);
                break;

            case ConditionOperator.NotEqual:
                condition = String.format("%s<>%s", attrName, parameter);
                break;

            case ConditionOperator.EqualUserId:
                condition = String.format("%s=%s", attrName, parameter);
                break;

            case ConditionOperator.NotEqualUserId:
                condition = String.format("%s<>%s", attrName, parameter);
                break;

            case ConditionOperator.BeginsWith:
                condition = String.format("%s LIKE %s", attrName, parameter);
                break;

            case ConditionOperator.DoesNotBeginWith:
                condition = String.format("%s NOT LIKE %s", attrName, parameter);
                break;

            case ConditionOperator.DoesNotContain:
                condition = String.format("%s NOT LIKE %s", attrName, parameter);
                break;

            case ConditionOperator.DoesNotEndWith:
                condition = String.format("%s NOT LIKE %s", attrName, parameter);
                break;

            case ConditionOperator.EndsWith:
                condition = String.format("%s LIKE %s", attrName, parameter);
                break;

            case ConditionOperator.GreaterEqual:
                condition = String.format("%s>=%s", attrName, parameter);
                break;

            case ConditionOperator.GreaterThan:
                condition = String.format("%s>%s", attrName, parameter);
                break;

            case ConditionOperator.LessEqual:
                condition = String.format("%s<=%s", attrName, parameter);
                break;

            case ConditionOperator.LessThan:
                condition = String.format("%s<%s", attrName, parameter);
                break;

            case ConditionOperator.Last7Days:
                //近7天
                condition = String.format("(DATEDIFF(DAY, %s, GETDATE())>0 AND DATEDIFF(DAY, %s, GETDATE())<=7)", attrName);
                break;

            case ConditionOperator.LastMonth:
                condition = String.format("DATEDIFF(MONTH, %s, GETDATE())=1", attrName);
                break;

            case ConditionOperator.LastWeek:
                condition = String.format("DATEDIFF(WEEK, %s, GETDATE())=1", attrName);
                break;

            case ConditionOperator.LastXDays:
                condition = String.format("(DATEDIFF(DAY, %s, GETDATE())>0 AND DATEDIFF(DAY, %s, GETDATE())<=%s)", attrName, parameter);
                break;

            case ConditionOperator.LastXHours:
                condition = String.format("(DATEDIFF(HH, %s, GETDATE())>0 AND DATEDIFF(HH, %s, GETDATE())<=%s)", attrName, parameter);
                break;

            case ConditionOperator.LastXMonths:
                condition = String.format("(DATEDIFF(MONTH, %s, GETDATE())>0 AND DATEDIFF(MONTH, %s, GETDATE())<=%s)", attrName, parameter);
                break;

            case ConditionOperator.LastXWeeks:
                condition = String.format("(DATEDIFF(WEEK, %s, GETDATE())>0 AND DATEDIFF(WEEK, %s, GETDATE())<=%s)", attrName, parameter);
                break;

            case ConditionOperator.LastXYears:
                condition = String.format("(DATEDIFF(YEAR, %s, GETDATE())>0 AND DATEDIFF(YEAR, %s, GETDATE())<=%s)", attrName, parameter);
                break;

            case ConditionOperator.LastYear:
                condition = String.format("DATEDIFF(YEAR, %s, GETDATE())=1", attrName);
                break;

            case ConditionOperator.Next7Days:
                condition = String.format("(DATEDIFF(DAY, GETDATE(), %s)>0 AND DATEDIFF(DAY, GETDATE(), %s)<=7)", attrName);
                break;

            case ConditionOperator.NextMonth:
                condition = String.format("DATEDIFF(MONTH, GETDATE(), %s)=1", attrName);
                break;

            case ConditionOperator.NextWeek:
                condition = String.format("DATEDIFF(WEEK, GETDATE(), %s)=1", attrName);
                break;

            case ConditionOperator.NextXDays:
                condition = String.format("(DATEDIFF(DAY, GETDATE(), %s)>0 AND DATEDIFF(DAY, GETDATE(), %s)<=%s)", attrName, parameter);
                break;

            case ConditionOperator.NextXHours:
                condition = String.format("(DATEDIFF(HOUR, GETDATE(), %s)>0 AND DATEDIFF(HOUR, GETDATE(), %s)<=%s)", attrName, parameter);
                break;

            case ConditionOperator.NextXMonths:
                condition = String.format("(DATEDIFF(MONTH, GETDATE(), %s)>0 AND DATEDIFF(MONTH, GETDATE(), %s)<=%s)", attrName, parameter);
                break;

            case ConditionOperator.NextXWeeks:
                condition = String.format("(DATEDIFF(WEEK, GETDATE(), %s)>0 AND DATEDIFF(WEEK, GETDATE(), %s)<=%s)", attrName, parameter);
                break;

            case ConditionOperator.NextXYears:
                condition = String.format("(DATEDIFF(YEAR, GETDATE(), %s)>0 AND DATEDIFF(YEAR, GETDATE(), %s)<=%s)", attrName, parameter);
                break;

            case ConditionOperator.NextYear:
                condition = String.format("DATEDIFF(YEAR, GETDATE(), %s)=1", attrName);
                break;

            case ConditionOperator.Today:
                condition = String.format("to_char(%s, 'yyyy-mm-dd')=to_char(now(), 'yyyy-mm-dd')", attrName);
                break;

            case ConditionOperator.NotBetween:
                condition = String.format("%s NOT BETWEEN TO_DATE('%s','yyyy-mm-dd') AND TO_DATE('%s','yyyy-mm-dd')", attrName, parameter, parameter);
                break;

            case ConditionOperator.Between:
                condition = String.format("%s BETWEEN TO_DATE('%s','yyyy-mm-dd') AND TO_DATE('%s','yyyy-mm-dd')", attrName, parameter, parameter);
                break;

            case ConditionOperator.OlderThanXMonths:
                condition = String.format("DATEDIFF(MONTH, %s, GETDATE())>=%s", attrName, parameter);
                break;

            case ConditionOperator.On:
                condition = String.format("%s=%s", attrName, parameter);
                break;

            case ConditionOperator.NotOn:
                condition = String.format("%s<>%s", attrName, parameter);
                break;

            case ConditionOperator.OnOrAfter:
                condition = String.format("DATEDIFF(DAY, %s, GETDATE())<=%s", attrName, parameter);
                break;

            case ConditionOperator.OnOrBefore:
                condition = String.format("DATEDIFF(DAY, %s, GETDATE())>=%s", attrName, parameter);
                break;

            case ConditionOperator.After:
                condition = String.format("DATEDIFF(DAY, %s, GETDATE())<%s", attrName, parameter);
                break;

            case ConditionOperator.Before:
                condition = String.format("DATEDIFF(DAY, %s, GETDATE())>%s", attrName, parameter);
                break;

            case ConditionOperator.OnOrAfterToday:
                condition = String.format("DATEDIFF(DAY, %s, GETDATE())<=0", attrName);
                break;

            case ConditionOperator.OnOrBeforeToday:
                condition = String.format("DATEDIFF(DAY, %s, GETDATE())>=0", attrName);
                break;

            case ConditionOperator.AfterToday:
                condition = String.format("DATEDIFF(DAY, %s, GETDATE())<0", attrName);
                break;

            case ConditionOperator.BeforeToday:
                condition = String.format("DATEDIFF(DAY, %s, GETDATE())>0", attrName, parameter);
                break;

            case ConditionOperator.ThisMonth:
                condition = String.format("DATEDIFF(MONTH, %s, GETDATE())=0", attrName);
                break;

            case ConditionOperator.ThisWeek:
                condition = String.format("DATEDIFF(WEEK, %s, GETDATE())=0", attrName);
                break;

            case ConditionOperator.ThisYear:
                condition = String.format("DATEDIFF(YEAR, %s, GETDATE())=0", attrName);
                break;

            case ConditionOperator.Tomorrow:
                condition = String.format("DATEDIFF(MONTH, %s, GETDATE())=-1", attrName);
                break;

            case ConditionOperator.Yesterday:
                condition = String.format("DATEDIFF(MONTH, %s, GETDATE())=1", attrName);
                break;

            case ConditionOperator.OlderThanXYears:
                condition = String.format("DATEDIFF(YEAR, %s, GETDATE())>=%s", attrName, parameter);
                break;

            case ConditionOperator.OlderThanXDays:
                condition = String.format("DATEDIFF(DAY, %s, GETDATE())>=%s", attrName, parameter);
                break;

            case ConditionOperator.AfterXYears:
                condition = String.format("DATEDIFF(YEAR, %s, GETDATE())<=%s", attrName, parameter);
                break;

            case ConditionOperator.AfterXMonths:
                condition = String.format("DATEDIFF(MONTH, %s, GETDATE())<=%s", attrName, parameter);
                break;

            case ConditionOperator.AfterXDays:
                condition = String.format("DATEDIFF(DAY, %s, GETDATE())<=%s", attrName, parameter);
                break;

            case ConditionOperator.NotIn:
                parameter = StringUtil.join(conditionNode.Values.stream().map(x -> "?").collect(Collectors.toList()), ",");
                condition = String.format("%s NOT IN(%s)", attrName, parameter);
                break;

            case ConditionOperator.In:
                parameter = StringUtil.join(conditionNode.Values.stream().map(x -> "?").collect(Collectors.toList()), ",");
                condition = String.format("%s IN(%s)", attrName, parameter);
                break;

            case ConditionOperator.Like:
                condition = String.format("%s LIKE %s", attrName, parameter);
                break;

            case ConditionOperator.Contains:
                condition = String.format("%s LIKE %s", attrName, parameter);
                break;

            case ConditionOperator.NotLike:
                condition = String.format("%s NOT LIKE %s", attrName, parameter);
                break;

            case ConditionOperator.Null:
                condition = String.format("%s IS NULL", attrName);
                break;

            case ConditionOperator.NotNull:
                condition = String.format("%s IS NOT NULL", attrName);
                break;

            case ConditionOperator.EqualBusinessId:
                condition = String.format("%s=%s", attrName, parameter);
                break;

            case ConditionOperator.NotEqualBusinessId:
                condition = String.format("%s<>%s", attrName, parameter);
                break;

            case ConditionOperator.EqualOrganizationId:
                condition = String.format("%s=%s", attrName, parameter);
                break;

            case ConditionOperator.NotEqualOrganizationId:
                condition = String.format("%s<>%s", attrName, parameter);
                break;

            default:
                break;
        }

        return condition;
    }

    private void addParamValue(RetrieverParameter retrieverParameter, Attribute attribute, ConditionExpression conditionNode, QueryParameters sqlParameters) throws XmsException {
        ConditionExpressionValidator.checkValue(conditionNode);
        switch (conditionNode.Operator) {
            case ConditionOperator.Equal:
            case ConditionOperator.NotEqual:
                sqlParameters.args.add(ValueConverter.toAttributeType(attribute, conditionNode.Values.get(0)));
                break;

            case ConditionOperator.EqualUserId:
            case ConditionOperator.NotEqualUserId:
                sqlParameters.args.add(retrieverParameter.getCurrentUser().getUser().SystemUserId);
                break;

            case ConditionOperator.BeginsWith:
            case ConditionOperator.DoesNotBeginWith:
                sqlParameters.args.add(conditionNode.Values.get(0).toString() + "%");
                break;

            case ConditionOperator.DoesNotContain:
                sqlParameters.args.add("%" + conditionNode.Values.get(0).toString() + "%");
                break;

            case ConditionOperator.EndsWith:
            case ConditionOperator.DoesNotEndWith:
                sqlParameters.args.add("%" + conditionNode.Values.get(0).toString());
                break;

            case ConditionOperator.GreaterEqual:
            case ConditionOperator.GreaterThan:
            case ConditionOperator.LessEqual:
            case ConditionOperator.LessThan:
                sqlParameters.args.add(ValueConverter.toAttributeType(attribute, conditionNode.Values.get(0)));
                break;

            case ConditionOperator.LastXYears:
            case ConditionOperator.LastXWeeks:
            case ConditionOperator.LastXDays:
            case ConditionOperator.LastXHours:
            case ConditionOperator.LastXMonths:
            case ConditionOperator.NextXYears:
            case ConditionOperator.NextXMonths:
            case ConditionOperator.NextXWeeks:
            case ConditionOperator.NextXDays:
            case ConditionOperator.NextXHours:
            case ConditionOperator.OlderThanXMonths:
            case ConditionOperator.OlderThanXYears:
            case ConditionOperator.OlderThanXDays:
            case ConditionOperator.AfterXYears:
            case ConditionOperator.AfterXMonths:
            case ConditionOperator.AfterXDays:
                sqlParameters.args.add(Integer.parseInt(conditionNode.Values.get(0).toString()));
                break;

            case ConditionOperator.Between:
            case ConditionOperator.NotBetween:
                sqlParameters.args.add(ValueConverter.toAttributeType(attribute, conditionNode.Values.get(0)));
                sqlParameters.args.add(ValueConverter.toAttributeType(attribute, conditionNode.Values.get(1)));
                break;

            case ConditionOperator.On:
            case ConditionOperator.NotOn:
            case ConditionOperator.OnOrAfter:
            case ConditionOperator.OnOrBefore:
            case ConditionOperator.After:
            case ConditionOperator.Before:
                sqlParameters.args.add(ValueConverter.toAttributeType(attribute, conditionNode.Values.get(0)));
                break;

            case ConditionOperator.In:
            case ConditionOperator.NotIn:
                sqlParameters.args.addAll(conditionNode.Values);
                break;

            case ConditionOperator.Contains:
            case ConditionOperator.Like:
            case ConditionOperator.NotLike:
                sqlParameters.args.add("%" + conditionNode.Values.get(0).toString() + "%");
                break;

            case ConditionOperator.EqualBusinessId:
            case ConditionOperator.NotEqualBusinessId:
                sqlParameters.args.add(retrieverParameter.getCurrentUser().getUser().BusinessUnitId);
                break;

            case ConditionOperator.EqualOrganizationId:
            case ConditionOperator.NotEqualOrganizationId:
                sqlParameters.args.add(retrieverParameter.getCurrentUser().getUser().OrganizationId);
                break;

            default:
                //parameters.args.add(conditionNode.Values.get(0).toString());
                break;
        }
    }

    private void parseLinkEntity(RetrieverParameter retrieverParameter, QueryMetadataDescriptor metadataDescriptor, LinkEntity linkEntity, List<String> tableList, List<String> attrList, List<String> filterList, QueryParameters sqlParameters) throws XmsException {
        if (linkEntity.Columns.AllColumns && linkEntity.Columns.Columns.size() == 0) {
            Iterator<Attribute> attrIt = metadataDescriptor.attributeList.stream().filter(x -> x.getEntityname().equalsIgnoreCase(linkEntity.LinkToEntityName)).iterator();
            while (attrIt.hasNext()) {
                linkEntity.Columns.addColumn(attrIt.next().getName());
            }
        }
        String entityAlias = linkEntity.EntityAlias;
        for (String column : linkEntity.Columns.Columns) {
            //guid类型的字段值，将替换为对应的名称字段//link entity like alias.name
            String field = column.toLowerCase();
            Attribute attr = metadataDescriptor.attributeList.stream().filter(x -> x.getName().equalsIgnoreCase(column) && x.getEntityname().equalsIgnoreCase(linkEntity.LinkToEntityName)).findFirst().get();
            if (linkEntity.Columns.ColumnFormatting.containsKey(field))//if has formatting
            {
                String formatting = linkEntity.Columns.ColumnFormatting.get(field);
                attrList.add((wrapField(formatting) + getColumnAsSegment(entityAlias, field + "_formatting")).toLowerCase());
            }
            if (attr != null) {
                boolean nullRead = retrieverParameter.getNoneReadFields() != null ? retrieverParameter.getNoneReadFields().stream().anyMatch(x -> x.getAttributeid().equals(attr.getAttributeid())) : false;
                AttributeAlias attrAlias = new AttributeAlias();
                attrAlias.EntityName = linkEntity.LinkToEntityName;
                attrAlias.EntityAlias = entityAlias;
                attrAlias.Name = attr.getName();
                attrAlias.Alias = entityAlias + getAliasJoiner() + attr.getName();
                metadataDescriptor.attributeAlias.add(attrAlias);
                setFieldSegment(attrList, attr, entityAlias, field, nullRead, retrieverParameter.isIncludeNameField());
            }
        }
        Entity entity = metadataDescriptor.entityList.stream().filter(n -> n.getName().equalsIgnoreCase(linkEntity.LinkToEntityName)).findFirst().get();
        String joinCondition = wrapField(entityAlias) + "." + wrapField(linkEntity.LinkToAttributeName) + " = "
                + wrapField(StringUtil.isEmpty(linkEntity.FromEntityAlias) ? linkEntity.LinkFromEntityName : linkEntity.FromEntityAlias)
                + "." + wrapField(linkEntity.LinkFromAttributeName);
        if (entity.getAuthorizationenabled() && retrieverParameter.getCurrentUser() != null) {
            if (!retrieverParameter.getCurrentUser().isAdministrator() && entity.getAuthorizationenabled() && CollectionUtil.isEmpty(retrieverParameter.getEntityPermissions())) {
                joinCondition += " AND 1=2";
            } else if (!retrieverParameter.getCurrentUser().isAdministrator() &&
                    retrieverParameter.getEntityPermissions().stream().anyMatch(n -> n.getEntityId().equals(entity.getEntityid()) && n.getAccessRight() == AccessRightValue.Read.value())) {
                RoleEntityPermission prv = retrieverParameter.getEntityPermissions().stream().filter(n -> n.getEntityId().equals(entity.getEntityid()) && n.getAccessRight() == AccessRightValue.Read.value()).findFirst().get();
                //basic
                if (prv != null && entity.getEntitymask() == EntityMask.User.value() && prv.getAccessRightsMask() == EntityPermissionDepth.Self) {
                    joinCondition += String.format(" AND %s.ownerid='%s'", wrapField(entityAlias)
                            , retrieverParameter.getCurrentUser().getUser().SystemUserId);
                    //sqlParameters.args.add(retrieverParameter.getCurrentUser().getUser().SystemUserId);
                }
                //local
                else if (prv != null && entity.getEntitymask() == EntityMask.User.value() && prv.getAccessRightsMask() == EntityPermissionDepth.BusinessUnit) {
                    joinCondition += String.format(" AND %s.owningbusinessunit='%s'", wrapField(entityAlias)
                            , retrieverParameter.getCurrentUser().getUser().BusinessUnitId);
                    //sqlParameters.args.add(retrieverParameter.getCurrentUser().getUser().BusinessUnitId);
                }
                //deep
                else if (prv != null && entity.getEntitymask() == EntityMask.User.value() && prv.getAccessRightsMask() == EntityPermissionDepth.BusinessUnitAndChild) {
                    joinCondition += String.format(" AND %s.owningbusinessunit in(SELECT businessunitid FROM ufn_org_getdepttree('%s'))", entityAlias
                            , retrieverParameter.getCurrentUser().getUser().BusinessUnitId);
                    //sqlParameters.args.add(retrieverParameter.getCurrentUser().getUser().BusinessUnitId);
                }
            }
        }
        String tb = getLinkType(linkEntity.JoinOperator) + " " + linkEntity.LinkToEntityName + "view AS " + wrapField(entityAlias)
                + " ON " + joinCondition;
        tableList.add(tb);

        parseFilter(retrieverParameter, metadataDescriptor, metadataDescriptor.entityList.stream().filter(n -> n.getName().equalsIgnoreCase(linkEntity.LinkToEntityName)).findFirst().get(), linkEntity.LinkCriteria.FilterOperator, linkEntity.LinkCriteria, entityAlias, filterList, sqlParameters);

        if (CollectionUtil.notEmpty(linkEntity.LinkEntities)) {
            for (LinkEntity le : linkEntity.LinkEntities) {
                parseLinkEntity(retrieverParameter, metadataDescriptor, le, tableList, attrList, filterList, sqlParameters);
            }
        }
    }

    private String getLinkType(JoinOperator joinOperator) {
        return "LEFT JOIN";
    }

    private void setFieldSegment(List<String> attrList, Attribute attr, String entityAlias, String field, boolean nullRead, boolean includeNameField) {
        String column = field;
        if (includeNameField && (AttributeExt.typeIsRef(attr) || AttributeExt.typeIsPrimaryKey(attr))) {
            if (AttributeExt.typeIsPrimaryKey(attr)) {
                field = "name";
            } else {
                field += "name";
            }
            if (nullRead) {
                attrList.add("null " + getColumnAsSegment(entityAlias, field));
            } else {
                attrList.add(wrapField(entityAlias) + "." + wrapField(field) + " " + getColumnAsSegment(entityAlias, field));
            }
        }
        if (AttributeExt.typeIsState(attr) || AttributeExt.typeIsBit(attr)) {
            if (nullRead) {
                attrList.add("null " + getColumnAsSegment(entityAlias, field));
                attrList.add(String.format("null %s", getColumnAsSegment(entityAlias, field + "name")));
            } else {
                attrList.add(wrapField(entityAlias) + "." + wrapField(field) + " " + getColumnAsSegment(entityAlias, field));
                field = String.format("(CASE %s WHEN '0' THEN '%s' WHEN '1' THEN '%s' ELSE '' END) %s"
                        , wrapField(entityAlias) + "." + wrapField(field)
                        , attr.getPicklists().stream().filter(n -> n.getValue() == 0).findFirst().get().getName()
                        , attr.getPicklists().stream().filter(n -> n.getValue() == 1).findFirst().get().getName()
                        , getColumnAsSegment(entityAlias, field + "name"));
                attrList.add(field);
            }
        } else if (AttributeExt.typeIsPickList(attr)) {
            if (nullRead) {
                attrList.add("null " + getColumnAsSegment(entityAlias, field));
                attrList.add(String.format("null %s", getColumnAsSegment(entityAlias, field + "name")));
            } else {
                attrList.add(wrapField(entityAlias) + "." + wrapField(field) + " " + getColumnAsSegment(entityAlias, field));
                String pField = String.format("(CASE %s", wrapField(entityAlias) + "." + wrapField(field));
                for (OptionSetDetail ops : attr.getOptionset().getItems()) {
                    pField += String.format(" WHEN %s THEN '%s' ", ops.getValue(), ops.getName());
                }
                pField += String.format("ELSE '' END) %s", getColumnAsSegment(entityAlias, field + "name"));
                attrList.add(pField);
            }
        } else if (AttributeExt.typeIsDecimal(attr) || AttributeExt.typeIsFloat(attr) || AttributeExt.typeIsMoney(attr)) {
            if (nullRead) {
                attrList.add(String.format("null %s", getColumnAsSegment(entityAlias, field)));
            } else {
                attrList.add("CAST(ROUND(" + wrapField(entityAlias) + "." + wrapField(field) + "," + attr.getPrecision() + ") AS " + AttributeExt.getDbType(attr) + ") AS " + wrapField(field));
            }
        } else {
            if (nullRead) {
                attrList.add(String.format("null %s", getColumnAsSegment(entityAlias, column)));
            } else {
                attrList.add(wrapField(entityAlias) + "." + wrapField(column) + " " + getColumnAsSegment(entityAlias, column));
            }
        }
    }

    private String getColumnAsSegment(String entityAlias, String column) {
        return " AS \"" + entityAlias + getAliasJoiner() + column + "\"";
    }

    private String wrapField(String field) {
        return field;
    }

    private String getAliasJoiner() {
        return ".";
    }
}
